package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;

public class ReviewProcessModel {
	
	private static String reviewBySpecialization ="SELECT Specialization.name, count(*),Specialization.id "+
    "FROM Students,Specialization " +
    "WHERE Students.specialization=Specialization.id  "+
    "GROUP BY Specialization.name,Specialization.id";
	
	
	private static String reviewByDiscipline = " SELECT Discipline.name, count(*),Discipline.id "+
                          "FROM  Degrees,Discipline " +
                          "WHERE Degrees.discipline=Discipline.id  "+
                          "GROUP BY Discipline.name,Discipline.id";
	
   	
	public static CachedRowSet reviewBySpecialization() throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(reviewBySpecialization);
		
			CachedRowSet crsSpecialization = new CachedRowSetImpl();
			crsSpecialization.populate(rs);
			
        rs.close();
        stmt.close();
        conn.close();
        return crsSpecialization;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet reviewByDiscipline() throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(reviewByDiscipline);
		
			CachedRowSet crsDiscipline = new CachedRowSetImpl();
			crsDiscipline.populate(rs);
			
        rs.close();
        stmt.close();
        conn.close();
        return crsDiscipline;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	

}

